package com.yu.dv.strategy.metadata.impl;

import com.yu.dv.entity.ColumnInfo;
import com.yu.dv.entity.IndexInfo;
import com.yu.dv.entity.TableInfo;
import com.yu.dv.strategy.metadata.MetadataTemplate;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class OracleMetadataStrategy extends MetadataTemplate {

    @Override
    protected String getCatalog(Connection connection) {
        // Oracle中catalog通常为null
        return null;
    }

    @Override
    protected String getSchema(Connection connection) {
        try {
            // Oracle使用当前用户名作为schema
            return connection.getMetaData().getUserName().toUpperCase();
        } catch (Exception e) {
            // 如果获取失败，尝试从连接属性中获取
            try {
                String url = connection.getMetaData().getURL();
                if (url != null && url.contains("user=")) {
                    // 从URL中解析用户名
                    return extractUsernameFromUrl(url);
                }
            } catch (Exception ex) {
                // 忽略异常
            }
            return null;
        }
    }

    @Override
    protected String[] getTableTypes() {
        return new String[]{"TABLE", "VIEW"};
    }

    @Override
    protected String[][] getParameterCombinations(String catalog, String schema) {
        // Oracle特定的参数组合
        return new String[][]{
                {catalog, schema},        // 原始参数
                {null, schema},           // 仅schema（Oracle主要使用schema）
                {null, null},             // 无限制
                {"%", "%"}               // 通配符
        };
    }

    @Override
    protected List<TableInfo> getTablesWithFallback(Connection connection, String catalog, String schema) throws Exception {
        List<TableInfo> tables = new ArrayList<>();

        // Oracle备用查询：使用数据字典视图
        String currentSchema = getSchema(connection);
        if (currentSchema == null) {
            currentSchema = "USER"; // 使用USER视图
        }

        String query = buildOracleFallbackQuery(currentSchema);

        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                String tableName = rs.getString("TABLE_NAME");
                String tableType = rs.getString("TABLE_TYPE");
                String remarks = rs.getString("REMARKS");

                TableInfo table = new TableInfo(tableName, tableType, remarks);
                tables.add(table);
            }
        }

        log.info("Oracle备用方案获取到 {} 张表", tables.size());
        return tables;
    }

    /**
     * 构建Oracle备用查询
     */
    private String buildOracleFallbackQuery(String schema) {
        if ("USER".equalsIgnoreCase(schema)) {
            // 查询当前用户的表
            return "SELECT table_name as TABLE_NAME, 'TABLE' as TABLE_TYPE, '' as REMARKS " +
                    "FROM user_tables " +
                    "UNION ALL " +
                    "SELECT view_name as TABLE_NAME, 'VIEW' as TABLE_TYPE, '' as REMARKS " +
                    "FROM user_views " +
                    "ORDER BY TABLE_NAME";
        } else {
            // 查询指定schema的表（需要相应权限）
            return "SELECT table_name as TABLE_NAME, 'TABLE' as TABLE_TYPE, '' as REMARKS " +
                    "FROM all_tables " +
                    "WHERE owner = '" + schema + "' " +
                    "UNION ALL " +
                    "SELECT view_name as TABLE_NAME, 'VIEW' as TABLE_TYPE, '' as REMARKS " +
                    "FROM all_views " +
                    "WHERE owner = '" + schema + "' " +
                    "ORDER BY TABLE_NAME";
        }
    }

    /**
     * Oracle特定的列信息获取（处理LONG、CLOB等特殊类型）
     */
    @Override
    protected List<ColumnInfo> getColumnsSafe(DatabaseMetaData metaData, String catalog,
                                              String schema, String tableName) throws SQLException {
        List<ColumnInfo> columns = super.getColumnsSafe(metaData, catalog, schema, tableName);

        // Oracle特殊处理：修正数据类型映射
        for (ColumnInfo column : columns) {
            String dataType = column.getDataType();
            if (dataType != null) {
                // 标准化Oracle数据类型
                column.setDataType(normalizeOracleDataType(dataType));
            }
        }

        return columns;
    }

    /**
     * 标准化Oracle数据类型
     */
    private String normalizeOracleDataType(String dataType) {
        if (dataType == null) return dataType;

        // Oracle数据类型映射
        switch (dataType.toUpperCase()) {
            case "VARCHAR2":
                return "VARCHAR";
            case "NUMBER":
                return "NUMERIC";
            case "CLOB":
            case "NCLOB":
                return "LONGVARCHAR";
            case "BLOB":
                return "LONGVARBINARY";
            case "TIMESTAMP(6)":
            case "TIMESTAMP(6) WITH TIME ZONE":
                return "TIMESTAMP";
            default:
                return dataType;
        }
    }

    /**
     * 从JDBC URL中提取用户名
     */
    private String extractUsernameFromUrl(String url) {
        try {
            // 解析类似 jdbc:oracle:thin:scott/tiger@localhost:1521:orcl 的URL
            if (url.contains(":")) {
                String[] parts = url.split(":");
                if (parts.length >= 4) {
                    String userInfo = parts[3];
                    if (userInfo.contains("/")) {
                        String username = userInfo.split("/")[0];
                        if (!username.contains("@")) {
                            return username.toUpperCase();
                        }
                    }
                }
            }

            // 解析类似 jdbc:oracle:thin:@localhost:1521:orcl 的URL（用户名在属性中）
            if (url.contains("user=")) {
                String[] params = url.split(";");
                for (String param : params) {
                    if (param.trim().startsWith("user=")) {
                        return param.split("=")[1].toUpperCase();
                    }
                }
            }
        } catch (Exception e) {
            log.debug("从URL解析用户名失败: {}", e.getMessage());
        }

        return null;
    }

    /**
     * Oracle特定的索引信息处理
     */
    @Override
    protected List<IndexInfo> getIndexesSafe(DatabaseMetaData metaData, String catalog,
                                             String schema, String tableName) throws SQLException {
        // Oracle的索引信息获取需要特殊处理
        List<IndexInfo> indexes = new ArrayList<>();

        try (ResultSet rs = metaData.getIndexInfo(catalog, schema, tableName, false, true)) {
            while (rs.next()) {
                String indexName = rs.getString("INDEX_NAME");
                if (indexName == null) continue;

                // Oracle中系统索引跳过
                if (indexName.toUpperCase().startsWith("SYS_")) {
                    continue;
                }

                String columnName = rs.getString("COLUMN_NAME");
                boolean nonUnique = rs.getBoolean("NON_UNIQUE");
                short type = rs.getShort("TYPE");
                short ordinalPosition = rs.getShort("ORDINAL_POSITION");

                IndexInfo index = new IndexInfo(indexName, columnName, nonUnique, type, ordinalPosition);
                indexes.add(index);
            }
        }

        return indexes;
    }
}